package com.hnkywl.sts.dao.jhgl;

import com.hnkywl.sts.entity.jhgl.Bgjhhzmx;
import com.hnkywl.sts.service.sys.YgService;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: ZHUCHG
 * Date: 13-6-28
 * Time: 上午10:43
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class BgjhhzmxDao extends SimpleHibernateTemplate<Bgjhhzmx, Long> {
    //根据汇总保供计划ID查询汇总计划明细
    public List<Map> findList(Long id, Integer zt) {
        //物资编码 物资名称 型号规格 单位 需求计划数量 本站库存数量  一级平衡数量 上报计划数量 退回原因 到货日期 备注  计划员
        String sql = "select mx.id \"id\", mx.bgjhhz_id \"bgjhhz.id\",mx.wzgg_id \"wzgg.id\",gg.code \"wzgg.code\",gg.name \"wzgg.name\"," +
                " gg.cz||' '||gg.ggxh \"wzgg.ggxh\",gg.th \"wzgg.th\", gg.oldcode \"wzgg.oldcode\",gg.dw \"wzgg.dw\",mx.jhsl \"jhsl\"," +
                " nvl((select sum(kh.sl) from t_biz_bgjhhz hz " +
                " left join t_sys_lj lj on hz.lj_id=lj.id and lj.deleted=0 " +
                " left join t_sys_gsjg gyz on lj.gyz_id=gyz.id and gyz.deleted=0 " +
                " left join t_sys_ck ck on gyz.id=ck.ssgyz_id and ck.deleted=0 " +
                " left join t_biz_kh kh on kh.ck_id=ck.id and kh.deleted=0 " +
                " where hz.id= mx.bgjhhz_id and kh.jtwz_id=mx.wzgg_id),0) \"kcsl\"," +
                " 0 as \"sl1\",mx.jhsl as \"sbsl\",mx.thyy  \"thyy\",mx.thrq \"thrq\",thr \"thr\",mx.dhrq  \"dhrq\",mx.ph \"ph\",mx.sbcl  \"sbcl\",mx.bz \"bz\",mx.bgjhmx_id \"bgjhmx.id\"," +
                " yg.id \"createUser.id\",yg.xm \"createUser.xm\"" +
                " from t_biz_bgjhhzmx mx " +
                " left join t_sys_gg gg on mx.wzgg_id=gg.id and gg.deleted=0 " +
                " left join t_sys_yg yg on mx.createuser_id=yg.id and yg.deleted=0 " +
                " where mx.bgjhhz_id=? and mx.zt=? and mx.deleted=0 order by gg.oldcode";
        List<Map> result = createSQLQuery(sql, id, zt).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return result;
    }

    public List<Map> findBgjhhzmxContent(Long id) {
        Criteria criteria = getSession().createCriteria(Bgjhhzmx.class)
                .createAlias("wzgg", "wzgg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("wzgg.id").as("wzgg.id"))
                        .add(Property.forName("wzgg.code").as("wzgg.code"))
                        .add(Property.forName("wzgg.oldcode").as("wzgg.oldcode"))
                        .add(Property.forName("wzgg.name").as("wzgg.name"))
                        .add(Property.forName("wzgg.cz").as("wzgg.cz"))
                        .add(Projections.sqlProjection("wzgg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("wzgg.dw").as("wzgg.dw"))
                        .add(Property.forName("cd.id").as("cd.id"))
                        .add(Property.forName("cd.z").as("cd.z"))
                        .add(Property.forName("ck").as("ck"))
                        .add(Property.forName("sbjs").as("sbjs"))
                        .add(Property.forName("sbsl").as("sbsl"))
                        .add(Projections.sqlProjection("22 as \"jzfs.id\"", new String[]{"jzfs.id"}, new Type[]{Hibernate.LONG}))
                        .add(Projections.sqlProjection("'理重' as \"jzfs.z\"", new String[]{"jzfs.z"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("ph").as("ph"))
                        .add(Property.forName("hsj").as("hsj"))
                        .add(Property.forName("bhsj").as("bhsj"))
                        .add(Property.forName("hsje").as("hsje"))
                        .add(Property.forName("xsxt_xsdh").as("xsxt_xsdh"))
                        .add(Property.forName("bz").as("bz"))
                ).add(Restrictions.eq("bgjhhz.id", id))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        criteria.addOrder(Property.forName("xsxt_xsdh").asc());
        return criteria.list();
    }

    /**
     * @param status   //未办理 status=1：zt=3并且到货时间大于当天 已办理status=2：zt>3 and zt<10  已过期status=3：zt=3并且到货时间小于当天  已退回status=4：zt=10
     * @param jhdwId   计划单位ID
     * @param wzdl     物资大类code
     * @param jhnf     计划年份
     * @param jhyf     计划月份
     * @param wzbm     物资编码
     * @param sm       说明
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Map> findYwkList(Integer status, Long jhdwId, String wzdl, Integer jhnf, Integer jhyf, Date dhrq1, Date dhrq2, String wzbm, String sm, String ywksbcl, int pageNo, int pageSize) {

        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sqlBase = "select mx.cgfs \"cgfs\",mx.ywkclrq \"ywkclrq\",mx.ywkclr_id as \"ywkclr.id\",ywkcl.xm \"ywkclr.xm\",mx.id \"id\",mx.wzgg_id \"wzgg.id\",gg.code \"wzgg.code\",gg.oldcode \"wzgg.oldcode\",gg.name \"wzgg.name\",gg.cz||' '||gg.ggxh \"wzgg.ggxh\",gg.th \"wzgg.th\",mx.jhsl \"jhsl\",mx.sl1 \"sl1\"," +
                "mx.sl2 \"sl2\",mx.sbsl \"sbsl\",mx.dhrq \"dhrq\",mx.ph \"ph\",mx.createuser_id \"createUser.id\",jhr.xm \"createUser.xm\",mx.bz \"bz\",nvl(mx.sfcg,0) as \"sfcg\",hz.type as \"bgjhhz.type\"," +
                "hz.sm \"bgjhhz.sm\",hz.jhsj \"bgjhhz.jhsj\",hz.dhck_id \"bgjhhz.dhck.id\",mx.gyzclrq \"gyzclrq\",mx.gyzclr_id \"gyzclr.id\",gyzry.xm \"gyzclr.xm\",mx.sbcl \"sbcl\",hz.id \"bgjhhz.id\"," +
                "mx.thrq \"thrq\",mx.thr \"thr\",thyy \"thyy\"," +
                "nvl((select sum(kh.kkdsl) from t_biz_bgjhhz hz " +
                " left join t_sys_lj lj on hz.lj_id=lj.id and lj.deleted=0 " +
                " left join t_sys_gsjg nbgs on lj.jyjg_id=nbgs.id and nbgs.deleted=0 and nbgs.type='nbgs' " +
                " left join t_sys_ck ck on nbgs.id=ck.ssgs_id and nvl(ck.ckjb,1)=1 and ck.deleted=0 " +
                " left join t_biz_kh kh on kh.ck_id=ck.id and kh.deleted=0 " +
                " where hz.id= mx.bgjhhz_id and kh.jtwz_id=mx.wzgg_id),0) \"kcsl\" " +
                " from t_biz_bgjhhz hz " +
                " left join t_biz_bgjhhzmx mx on mx.bgjhhz_id=hz.id and mx.deleted=0 " +
                " left join t_sys_yg ywkcl on mx.ywkclr_id=ywkcl.id " +
                " left join t_sys_gg gg on mx.wzgg_id=gg.id and gg.deleted=0 " +
                " left join t_sys_yg jhr on mx.createuser_id=jhr.id " +
                " left join t_sys_yg gyzry on mx.gyzclr_id=gyzry.id ";
//                " left join t_sys_yg thry on mx.thr_id=thry.id and thry.deleted=0 ";
//        condition.add(" mx.sbsl<>0 ");
        if (status != null) {
            //未办理 status=1：zt=3并且到货时间大于当天
            //已办理status=2：zt>3 and zt<10
            //已过期status=3：zt=3并且到货时间小于当天
            //已退回status=4：zt=10
            //导入失败status=-1：mx.sfcg=0 and mx.zt=4 and nvl(mx.cgfs,1)=1   导入失败  计划已经处理   采购方式为比价  可导入接口
            switch (status) {
                case 1:
                    condition.add(" mx.zt=3 and mx.dhrq>:dhrq ");
                    param.put("dhrq", new Date());
                    break;
                case 2:
                    condition.add(" mx.zt>3 and mx.zt<10 ");
                    break;
                case 3:
                    condition.add(" mx.zt=3 and mx.dhrq<=:dhrq ");
                    param.put("dhrq", new Date());
                    break;
                case 4:
                    condition.add(" mx.zt=10 ");
                    break;
                case -1:
                    condition.add(" mx.sfcg=0 and mx.zt=4 and nvl(mx.cgfs,1)=1 ");
                    break;
            }
        }
        if (jhdwId != null) {
            condition.add(" hz.jhdw_id=:jhdwId ");
            param.put("jhdwId", jhdwId);
        }
        if (existsSQLQuery("select 1 from t_sys_yg_bmfw where yg_id = ?", YgService.getCurrentYg().getId())) {
            sqlBase += " inner join t_sys_yg_bmfw bmfw on gg.pm_id=bmfw.pm_id ";
            condition.add(" bmfw.yg_id = :curYgId");
            param.put("curYgId", YgService.getCurrentYg().getId());
        } else {
            sqlBase += " inner join v_wzbm_gg_search bmfw on gg.id = bmfw.ggid ";
            condition.add("bmfw.gsid = :gsid");
            param.put("gsid", YgService.getCurrentYgGsId());
        }
        if (StringUtils.isNotEmpty(wzdl)) {
            condition.add(" gg.pm_id in (select t.id from T_SYS_PM t,T_SYS_RJWZBM r where  t.RJWZBM_ID = r.id and r.yjwzbm_id = :wzdl and t.deleted!=1 and r.deleted!=1) ");
            param.put("wzdl", wzdl);
        }
        if (StringUtils.isNotEmpty(ywksbcl)) {
            condition.add(" sbcl like :ywksbcl ");
            param.put("ywksbcl", ywksbcl + "%");
        }

        if (jhnf != null) {
            condition.add(" hz.jhnf=:jhnf ");
            param.put("jhnf", jhnf);
        }
        if (jhyf != null) {
            condition.add(" hz.jhyf=:jhyf ");
            param.put("jhyf", jhyf);
        }
        if (dhrq1 != null) {
            condition.add(" hz.jhsj>= :dhrq1 ");
            param.put("dhrq1", dhrq1);
        }
        if (dhrq2 != null) {
            condition.add(" hz.jhsj< :dhrq2 ");
            param.put("dhrq2", dhrq2);
        }
        if (StringUtils.isNotEmpty(wzbm)) {
            condition.add(" (gg.oldcode like :oldcode) ");
            param.put("oldcode", wzbm + "%");
        }
        if (StringUtils.isNotEmpty(sm)) {
            condition.add(" hz.sm like :sm ");
            param.put("sm", "%" + sm + "%");
        }
        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " where " + StringUtils.join(condition, " and ");
        String orderSql = " order by gg.oldcode,mx.bgjhhz_id,mx.date_created desc";
        String countSql = "select count(1) from (" + sqlBase + whereSql + ")";
        String resultSql = sqlBase + whereSql + orderSql;
        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }


    //根据汇总保供计划ID供应站未出里明细
    public boolean gyzwclmx(Long id, Integer zt) {
        String hql = "select id from Bgjhhzmx where zt=? and bgjhhz.id=?";
        return existsQuery(hql, zt, id);
    }

    public void updatesl2(Double sum, Long bgjhhzmxId) {
        String sql = "  update T_BIZ_BGJHHZMX set sl2=?, sbsl=jhsl-nvl(sl1,0)-? where id=?";//update T_BIZ_BGJHHZMX set sl2=?, sbsl=jhsl-sl1-? where id=? ";
        executeSQL(sql, sum, sum, bgjhhzmxId);
    }


    /**
     * 业务科二级平衡可用库存
     *
     * @param id
     * @return
     */
    public List<Map> findKcByBjjhhzmx(Long id) {
        String sql = "select ck.id \"ck.id\",ck.mc \"ck.mc\",kh.id \"kh.id\",kh.khbm \"kh.khbm\",kh.sl \"kh.sl\"," +
                "'' \"phsl\",kh.wsdj \"kh.wsdj\",kh.wsje \"kh.wsje\" " +
                " from t_biz_bgjhhzmx mx  " +
                " left join t_biz_bgjhhz hz on hz.id=mx.bgjhhz_id and hz.deleted=0" +
                " left join t_sys_lj lj on hz.lj_id = lj.id and lj.deleted = 0" +
                " left join t_sys_gsjg nbgs on lj.jyjg_id = nbgs.id and nbgs.deleted = 0 and nbgs.type = 'nbgs'" +
                " left join t_sys_ck ck on nbgs.id = ck.ssgs_id and nvl(ck.ckjb,1)=1 and ck.deleted = 0" +
                " left join t_biz_kh kh on kh.ck_id = ck.id and kh.jtwz_id = mx.wzgg_id  and kh.deleted = 0" +
                " left join t_biz_cgdmx cgdmx on cgdmx.kh_id=kh.id and cgdmx.deleted=0" +
                " where mx.id=? and mx.deleted=0 and nvl(kh.sl,0)>0";
        return createSQLQuery(sql, id).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
    }

    /**
     * 根据保供汇总计划明细ID查询是否有招标合同
     *
     * @param id
     * @return
     */
    public boolean sfybzbght(Long id) {
        String sql = "select count(mx.id) from t_biz_bgjhhzmx mx where mx.wzgg_id in " +
                " (select distinct htmx.wz_id as wzid from t_biz_htmx htmx,t_biz_ht ht " +
                " where ht.id=htmx.ht_id and ht.deleted=0 and htmx.deleted=0 and ht.zt='ysp'and ht.zbbj=2 and ht.cglx='bght' and ht.qdrq<=? and  ht.jsrq>=?) " +
                " and mx.id=?";
        Date date = new Date();
        BigDecimal count = (BigDecimal) createSQLQuery(sql, date, date, id).uniqueResult();
        return count.intValue() != 0;
    }

    /**
     * 通过物资编码ID将计划的采购方式改为招标，状态改为计划科已处理
     *
     * @param id
     */
    public void cgfsUpdate(Long id) {
        String sql = "update t_biz_bgjhhzmx mx set mx.zt=7,mx.cgfs=2 where mx.zt in (4,5,6) and mx.cgfs=1 and mx.deleted=0 and mx.wzgg_id=?";
        createSQLQuery(sql, id).executeUpdate();
    }
}
